'''
Created on Jun 21, 2011

@author: Zhao, Qi
'''

from common import server_log, utils
from common.memcache import Memcache
from data.dbpool import SLAVE_DBPOOL
from data.sql import SQL_SEL_VOUCHERS, SQL_SEL_USER_INFO, SQL_SEL_USER_BUNDLE, \
    SQL_SEL_USER_MANAGEMENT_BUNDLE, SQL_GET_LOCATION, SQL_SEL_FB_USER_BUNDLE
import oursql

def get_record_fromdb(id, tab, use_cache = True):
    if id is None:
        return None
    id = long(id)
    sql_strs = {'Vouchers': SQL_SEL_VOUCHERS,
                'User': SQL_SEL_USER_INFO,
                'Locations': SQL_GET_LOCATION
                } 
    
    keys = {'Vouchers': 'voucher_id',
            'User': 'user_id',
            'Locations': 'location_id'
            }
    
    if use_cache:
        memc = Memcache(86400)
        rec = memc.get("%s.%s" % (tab,id))
    else:
        rec = None
    
    if rec is not None:
        return rec
    
    else:
        try:
            # get connection from connection pool
            conn = SLAVE_DBPOOL.connection()
            cursor = conn.cursor(oursql.DictCursor)
    
            rec = {}
            
            #server_log.debug('[SQL BEGIN] %s [PARAM] %s' % (sql_strs[tab], id))
            
            cursor.execute(sql_strs[tab], (id,))
            for row in cursor:
                rec = row
            
            #server_log.debug('[SQL END] %s [PARAM] %s' % (sql_strs[tab], id))
            
            if rec:
                rec[keys[tab]] = id
            
        except Exception,e:
            server_log.error()
            return None
        else:
            if rec and use_cache:
                memc.set("%s.%s" % (tab,id), rec)
            return rec
        finally:
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()
        

def get_bundle_fromdb(res_list, tab, *dyn_params):
    res_list = res_list[:]
    sql_strs = {'Voucher': SQL_SEL_VOUCHERS,
                'User': SQL_SEL_USER_BUNDLE,
                'UserManagement': SQL_SEL_USER_MANAGEMENT_BUNDLE,
                'FBUser': SQL_SEL_FB_USER_BUNDLE,
                }
    keys = {'Voucher': 'voucher_id',
            'UserManagement': 'fb_user_id',
            'User': 'user_id',
            'FBUser': 'fb_user_id',
            }

    # load from memcache first
    memc = Memcache(86400)
    key_list = ['%s.%s' % (tab, res) for res in res_list]
    in_clause_ids = []
    mem_bundle = memc.get_multi(key_list)
    if ((mem_bundle is not None) and (len(mem_bundle) > 0)):
        for i in xrange(len(res_list)):
            rec = mem_bundle.get('%s.%s' % (tab, res_list[i]))
            if rec:
                res_list[i] = rec
            else:
                in_clause_ids.append(res_list[i])
    else:
        in_clause_ids = res_list
    
    # concat the in clause
    in_clause = utils.in_clause(in_clause_ids)
        
    if in_clause == '':
        return res_list
    else:    

        try:
            # get connection from connection pool
            conn = SLAVE_DBPOOL.connection()
            cursor = conn.cursor(oursql.DictCursor)
        
            #server_log.debug('[SQL BEGIN] %s' % (sql_strs[tab] % in_clause))
            
            if dyn_params:
                cursor.execute(sql_strs[tab] % in_clause, dyn_params)
            else:
                cursor.execute(sql_strs[tab] % in_clause)
            result_set = cursor.fetchall()
            
            #server_log.debug('[SQL END] %s' % (sql_strs[tab] % in_clause))
            
            rec_set = {}
            for row in result_set:
                # set into memcache
                memc.set('%s.%s' % (tab, row[keys[tab]]), row)
                # put into result set
                rec_set[row[keys[tab]]] = row
                
            # update the return list
            for i in xrange(len(res_list)):
                if type(res_list[i]) in (int, long):
                    res_list[i] = rec_set.get(res_list[i])
                   
            #server_log.debug('[DATA POPULATION END] %s' % (sql_strs[tab] % in_clause))
            
        except Exception,e:
            server_log.error()
            return None
        else:
            return res_list
        finally:
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close() 

# for update query sqls            
def insert_or_update_query(conn, sql_statment, sql_params, conn_close_flag = True):
    #server_log.debug("try to execute query %s with parameters %s " % (sql_statment, sql_params))        
    try:            
        cursor = conn.cursor()
        cursor.execute('SET AUTOCOMMIT = 0')        
        cursor.execute(sql_statment, sql_params)        
        conn.commit()
        return True
    except Exception, e:
        if conn is not None:
            conn.rollback()
        server_log.error('failed to execute the query')
        server_log.error(e)
        return False        
    finally:
        if 'cursor' in locals():
            cursor.close()
        if conn != None and conn_close_flag == True:
            conn.close()

# for select query sqls            
def select_query(conn, sql_statment, sql_params, dict_flag = False, sql_key = None, conn_close_flag = True):
    #server_log.debug("try to execute query %s with parameters %s " % (sql_statment, sql_params))
    try:        
        if (dict_flag == True):                
            cursor = conn.cursor(oursql.DictCursor)            
        else:
            cursor = conn.cursor()
        
        if (sql_params is not None):
            cursor.execute(sql_statment, sql_params)
        else:
            cursor.execute(sql_statment)
            
        if (sql_key is not None):
            result = {}            
            for row in cursor:
                result[row[sql_key]] = row
        else:
            result = cursor.fetchall()
                           
        return result
    
    except Exception, e:
        server_log.debug("failed to execute the query")
        server_log.error(e) 
    finally:
        if 'cursor' in locals():
            cursor.close()
        if conn != None and conn_close_flag == True:
            conn.close()
